﻿using System;
using System.Collections.Generic;
//using System.Data.Services;
//using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Runtime.CompilerServices;


//[assembly: SuppressIldasmAttribute()] 
namespace WcfRestSql
{
    public class WcfRestSql : IWcfRestSql
    {
        
        #region ISqlSpService Methods
        private void Authenticate()
        {
            bool isValid = false;
            if (HttpContext.Current.Request.Headers.AllKeys.Contains("Authenticate"))
            {
                string sKey = HttpContext.Current.Request.Headers["Authenticate"].ToString();
                if (sKey == "hrm;ise;áđâsđâsd")
                    isValid = true;
            }
            //
            if (!isValid)
                throw new Exception("Request is not authenticated!");
        }

        public DataSet runSqlDS(wcf_rest_sqlcommand sqlcmd)
        {
            DataSet ds = new DataSet();
            try
            {
                //HttpContext.Current.Response.Write(sqlcmd.paramlist.Length);
                //Authenticate();
                //


                if (sqlcmd.executetype.ToLower() == "" || sqlcmd.executetype.ToLower() == "execute") // not scaler or non-query
                    return runSqlCommand(sqlcmd);
                else // scaler or nonquery stored procedure
                    return runSqlCommandSingleValue(sqlcmd);

                //return null;
            }
            catch (SqlException sqle)
            {
                DataTable dt = new DataTable("ERROR");
                DataColumn workCol = dt.Columns.Add("Message", typeof(string));
                DataRow dr = dt.NewRow();
                dr["Message"] = sqle.Message;

                //string s = "sqlexeption: ";
                //for (int i = 0; i < sqlcmd.paramlist.Length; i++)
                //{
                //    s += " " + sqlcmd.paramlist[i].ToString();
                //}
                ////
                //dr["Message"] = s;
                //
                dt.Rows.Add(dr);
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception Exc)
            {
                DataTable dt = new DataTable("ERROR");
                DataColumn workCol = dt.Columns.Add("Message", typeof(string));
                DataRow dr = dt.NewRow();
                dr["Message"] = Exc.Message;

                //string s = "Exeption common";
                //for (int i = 0; i < sqlcmd.paramlist.Length; i++)
                //{
                //    s += " " + sqlcmd.paramlist[i].ToString();
                //}
                ////
                //dr["Message"] = s;

                dt.Rows.Add(dr);
                ds.Tables.Add(dt);
                return ds;
            }
            finally
            {
            }
        }

       
        public string runSqlXml(wcf_rest_sqlcommand sqlcmd)
        {
            try
            {
                Authenticate();
                //
                return runSqlDS(sqlcmd).GetXml(); // this runs tables together -- check if client can seperate
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }

        public string runSqlJson(wcf_rest_sqlcommand sqlcmd)
        {
            try
            {
                Authenticate();
                //
                DataSet ds = runSqlDS(sqlcmd);  // get results of cmd into dataset
                string jsonstring = "";
                foreach (DataTable dt in ds.Tables) // seperate tables 
                    jsonstring += dt.dataTableToJSON();
                return jsonstring;
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }
        #endregion

        DataSet runSqlCommand(wcf_rest_sqlcommand sqlcmd)  // run command, return resultset(s) in DataSet  
        {
            DataSet ds = new DataSet();   // DataSet will hold returned rows, if any
            DataTable dt = new DataTable("OUTPUT"); // used if we have output param table 
            DataTable dtData = new DataTable("DATA");
            string connectionString = ConfigurationManager.AppSettings["WorkingConnect"];
            bool haveOutputParams = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlDataAdapter da = new SqlDataAdapter(sqlcmd.command, conn))
                {
                    if (sqlcmd.commandtype.ToLower() == "text") // text query
                    {
                        da.SelectCommand.CommandType = CommandType.Text;
                        da.Fill(ds);
                    }
                    else // stored proc
                    {
                        da.SelectCommand.CommandType = CommandType.StoredProcedure;
                        da.SelectCommand = da.SelectCommand.processSqlParameters(sqlcmd);
                        //fill ds
                        da.Fill(ds);
                        
                        foreach (SqlParameter p in da.SelectCommand.Parameters)
                        {
                            if (p.Direction != ParameterDirection.Input) // output param
                            {
                                haveOutputParams = true;
                                DataRow dr = dt.NewRow();
                                DataColumn dc = dt.Columns.Add(p.ParameterName, typeof(string));
                                dr[p.ParameterName] = p.Value; // get value of output param
                                dt.Rows.Add(dr);
                            }
                        }
                        if (haveOutputParams)
                            ds.Tables.Add(dt); // add OUTPUT table to dataset, allows results sets + output values for client
                    }

                    conn.Close();
                }
            }
            return ds;
        }
        DataSet runSqlCommandSingleValue(wcf_rest_sqlcommand sqlcmd)  // run command, return scaler or non-query int value 
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            DataRow dr = dt.NewRow();


            DataTable dtOutput = new DataTable("OUTPUT"); // used if we have output param table 
            DataTable dtReturnValue = new DataTable("DATA");
            DataRow drReturnValue = dtReturnValue.NewRow();
            bool haveOutputParams = false;
            int returnValue;
            string connectionString = ConfigurationManager.AppSettings["WorkingConnect"];
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlcmd.command, conn);
                if (sqlcmd.commandtype.ToLower() == "text")
                    cmd.CommandType = CommandType.Text;
                else // stored proc, process parameters if any
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.processSqlParameters(sqlcmd); // extension method to get params from server

                    foreach (SqlParameter p in cmd.Parameters)
                    {
                        if (p.Direction != ParameterDirection.Input) // output param
                        {
                            haveOutputParams = true;
                            DataRow drOutput = dtOutput.NewRow();
                            DataColumn dcOutput = dtOutput.Columns.Add(p.ParameterName, typeof(string));
                            drOutput[p.ParameterName] = p.Value; // get value of output param
                            dtOutput.Rows.Add(drOutput);
                        }
                    }
                    
                }
                if (sqlcmd.executetype.ToLower() == "nonquery")
                {
                    returnValue = Convert.ToInt32(cmd.ExecuteNonQuery());
                    //DataColumn dc = dtReturnValue.Columns.Add("ReturnValue");
                    DataColumn dc = dtReturnValue.Columns.Add("RETVAL");
                    drReturnValue["RETVAL"] = returnValue; //Convert.ToString(returnValue);
                    dtReturnValue.Rows.Add(drReturnValue);
                    ds.Tables.Add(dtReturnValue);
                }
                else if (sqlcmd.executetype.ToLower() == "scaler")
                {
                    object scaler = cmd.ExecuteScalar();
                    //DataColumn dc = dtReturnValue.Columns.Add("Scaler");
                    DataColumn dc = dtReturnValue.Columns.Add("RETVAL");
                    drReturnValue["RETVAL"] = scaler; //Convert.ToString(scaler);
                    dtReturnValue.Rows.Add(drReturnValue);
                    ds.Tables.Add(dtReturnValue);
                }
                //

                conn.Close();
            }
            dt.Rows.Add(dr);
            ds.Tables.Add(dt);

            //
            if (haveOutputParams)
                ds.Tables.Add(dtOutput); // add OUTPUT table to dataset, allows results sets + output values for client
            //
            return ds;
        }
    }
}
